SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create Function [dbo].[OutAmount](@DrugId nvarchar(50),@fromdate datetime,@todate datetime)
Returns @OutAmount table
(
	Quantity int,
	Amount decimal
)
as
Begin
	declare @Out_total int		
	Insert @OutAmount select  sum(quantity) as Quantity,Sum(Quantity*UnitPrice) as TotalAmount
	from dbo.PrescriptionDetail AS PD INNER JOIN dbo.Prescription P ON P.PrescriptionID=PD.PrescriptionID
	where DeliveryDate between @fromdate and @todate and Drugid=@DrugId
	group by DrugId
	Return 
End

GO
